package ding.dao.Impl;

import ding.dao.BaseDao;
import ding.dao.BookDao;
import ding.entity.Book;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl extends BaseDao implements BookDao {

    @Override
    public List<Book> selectAll(String bookName, String author, int pageNum, int count) {
        List<Book> books = new ArrayList<>();

        StringBuffer sb = new StringBuffer();
        sb.append("select b.book_id bookId, ");
        sb.append("b.book_name bookName, ");
        sb.append("b.author, b.isbn, b.publisher,  ");
        sb.append("b.publisherDate, b.stock_quantity, ");
        sb.append("b.description,b.cover_image,b.book_type ");
        sb.append("FROM books b  where 1 = 1  ");
        List<Object> params = new ArrayList<>();
        if (bookName != null) {
            sb.append("AND book_name LIKE CONCAT('%',?,'%') ");
            params.add(bookName);
        }
        if (author != null) {
            sb.append("AND author LIKE CONCAT('%',?,'%') ");
            params.add(author);
        }
        sb.append(" limit ?,?");
        params.add((pageNum - 1) * count);
        params.add(count);
        //查询数据，返回结果集
        ResultSet resultSet = executeQuery(sb.toString(), params.toArray());

        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    Book book = new Book();
                    book.setBookId(resultSet.getInt("bookId"));
                    book.setBookName(resultSet.getString("bookName"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setIsbn(resultSet.getString("isbn"));
                    book.setPublisher(resultSet.getString("publisher"));
                    book.setPublisherDate(resultSet.getDate("publisherDate"));
                    book.setStockQuantity(resultSet.getInt("stock_quantity"));
                    book.setDescription(resultSet.getString("description"));
                    book.setCoverImage(resultSet.getString("cover_image"));
                    book.setBookType(resultSet.getInt("book_type"));
                    books.add(book);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(resultSet);
            }

        }
        return books;
    }

    @Override
    public int bookSize(String bookName, String author) {
        int count = 0;
        StringBuffer sb = new StringBuffer();
        sb.append("select count(1) count FROM books b  where 1 = 1  ");
        List<Object> params = new ArrayList<>();
        if (bookName != null) {
            sb.append("AND book_name LIKE CONCAT('%',?,'%') ");
            params.add(bookName);
        }
        if (author != null) {
            sb.append("AND author LIKE CONCAT('%',?,'%') ");
            params.add(author);
        }
        //查询数据，返回结果集
        ResultSet resultSet = executeQuery(sb.toString(), params.toArray());
        if (resultSet != null) {
            try {
                while (resultSet.next()) {

                    count = resultSet.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(resultSet);
            }

        }
        return count;
    }

    @Override
    public int add(Book book) {
        StringBuffer sb = new StringBuffer();
        sb.append(" INSERT INTO books(");
        sb.append(" books.book_name,books.author,");
        sb.append(" books.isbn,books.publisher,");
        sb.append(" books.publisherDate,books.stock_quantity,");
        sb.append(" books.description,books.cover_image,books.book_type)");
        sb.append(" VALUES(?,?,?,?,?,?,?,?,?)");
        return executeUpdate(sb.toString(), book.getBookName(), book.getAuthor(), book.getIsbn(),
                book.getPublisher(), book.getPublisherDate(), book.getStockQuantity(),
                book.getDescription(), book.getCoverImage(), book.getBookType());
    }


    @Override
    public Book selectById(Integer id) {
        String sql = "SELECT * from books WHERE book_id = " + id;
        Book book = new Book();
        //查询数据,返回结果集
        ResultSet resultSet = executeQuery(sql);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    book.setBookId(resultSet.getInt("book_id"));
                    book.setBookName(resultSet.getString("book_name"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setIsbn(resultSet.getString("isbn"));
                    book.setPublisher(resultSet.getString("publisher"));
                    book.setPublisherDate(resultSet.getDate("publisherDate"));
                    book.setStockQuantity(resultSet.getInt("stock_quantity"));
                    book.setDescription(resultSet.getString("description"));
                    book.setCoverImage(resultSet.getString("cover_image"));
                    book.setBookType(resultSet.getInt("book_type"));
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(resultSet);
            }
        }
        return book;
    }

    @Override
    public int updateBook(Book book) {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE books SET books.book_name = ?,");
        sb.append(" books.author = ?,");
        sb.append(" books.publisher = ?,books.publisherDate = ?,");
        sb.append(" books.stock_quantity = ?,books.description = ?,");
        sb.append(" books.cover_image = ?,books.book_type = ?");
        sb.append(" WHERE book_id = ?");

        Object[] params = {book.getBookName(), book.getAuthor(),
                book.getPublisher(), book.getPublisherDate(), book.getStockQuantity(),
                book.getDescription(), book.getCoverImage(), book.getBookType(), book.getBookId()};

        return executeUpdate(sb.toString(), params);
    }

    @Override
    public int delBook(Integer id) {
        String sql = "DELETE FROM books WHERE book_id = ?";

        return executeUpdate(sql, id);
    }
}
